package com.hymjweb.demo.hymj.master_bill_manage.pull.detail.mapper;

import com.hymjweb.demo.framework.sys.SysConstant;
import com.hymjweb.demo.framework.util.DataTypeUtil;
import com.hymjweb.demo.hymj.master_bill_manage.load.mapper.BNativeBillLoadMapper;
import com.hymjweb.demo.hymj.master_bill_manage.pull.detail.bean.BMasterBillPullDetailPojo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @BelongsProject: demo
 * @BelongsPackage: com.hymjweb.demo.hymj.master_bill_manage.pull.detail.mapper
 * @Author: luoge
 * @CreateTime: 2020-12-30 17:03
 * @Description:
 */

@Mapper
@Component
public interface BMasterBillPullDetailMapper {

    class BMasterBillPullDetailMapperProvider{

        public String insert(BMasterBillPullDetailPojo bMasterBillPullDetailPojo) {
            String sql = new SQL() {{
                INSERT_INTO("b_master_bill_pull_detail");
                VALUES("id","#{id}");
                VALUES("master_bill_pull_id","#{masterBillPullId}");
                VALUES("native_bill_id","#{nativeBillId}");
                VALUES("pull_number","#{pullNumber}");
                VALUES("pull_actual_weight","#{pullActualWeight}");
                VALUES("pull_chargeable_weight","#{pullChargeableWeight}");
                VALUES("pull_volume","#{pullVolume}");
                VALUES("pull_amount","#{pullAmount}");
                VALUES("pull_comment","#{pullComment}");

                VALUES("pull_dispose_id","#{pullDisposeId}");
                VALUES("pull_dispose_airport_id","#{pullDisposeAirportId}");
                VALUES("pull_dispose_airlines_id","#{pullDisposeAirlinesId}");
                VALUES("pull_dispose_flight_no","#{pullDisposeFlightNo}");
                VALUES("pull_dispose_flight_name","#{pullDisposeFlightName}");
                VALUES("pull_dispose_flight_date","#{pullDisposeFlightDate}");
                VALUES("pull_dispose_reason_ids","#{pullDisposeReasonIds}");
            }}.toString();
            return sql;
        }

        public String update(BMasterBillPullDetailPojo bMasterBillPullDetailPojo) {
            String sql= new SQL() {{
                UPDATE("b_master_bill_pull_detail t");
                SET("t.master_bill_pull_id = #{masterBillPullId}");
                SET("t.native_bill_id = #{nativeBillId}");
                SET("t.pull_number = #{pullNumber}");
                SET("t.pull_actual_weight = #{pullActualWeight}");
                SET("t.pull_chargeable_weight = #{pullChargeableWeight}");
                SET("t.pull_volume = #{pullVolume}");
                SET("t.pull_amount = #{pullAmount}");
                SET("t.pull_comment = #{pullComment}");

                SET("t.pull_dispose_id = #{pullDisposeId}");
                SET("t.pull_dispose_airport_id = #{pullDisposeAirportId}");
                SET("t.pull_dispose_airlines_id = #{pullDisposeAirlinesId}");
                SET("t.pull_dispose_flight_no = #{pullDisposeFlightNo}");
                SET("t.pull_dispose_flight_name = #{pullDisposeFlightName}");
                SET("t.pull_dispose_flight_date = #{pullDisposeFlightDate}");
                SET("t.pull_dispose_reason_ids = #{pullDisposeReasonIds}");

                WHERE("t.id = #{id}");
            }}.toString();
            return sql;
        }

        public String delete(String id){
            String sql = new SQL() {{
                DELETE_FROM("b_master_bill_pull_detail t");
                WHERE("t.id = #{id}");
            }}.toString();
            return sql;
        }

        public String deleteByMasterBillId(String masterBillId){
            String sql = new SQL() {{
                DELETE_FROM("b_master_bill_pull_detail t");
                WHERE("t.master_bill_pull_id in (select t.id from b_master_bill_pull t where t.master_bill_id = #{masterBillId})");
            }}.toString();
            return sql;
        }


        public String queryPullDetailList(Map param){
            String sql= new SQL() {{
                SELECT("t.* ");
                FROM(" b_master_bill_pull_detail t ");
                WHERE("1=1 ");
            }}.toString();

            if(param!=null){
                String masterBillPullId = DataTypeUtil.toStringObject(param.get("masterBillPullId"));
                String nativeBillId = DataTypeUtil.toStringObject(param.get("nativeBillId"));
                if(masterBillPullId!=null){
                    sql += " and t.master_bill_pull_id = " + DataTypeUtil.quoteStr(masterBillPullId);
                }
                if(nativeBillId!=null){
                    sql += " and t.native_bill_id = " + DataTypeUtil.quoteStr(nativeBillId);
                }
            }

            return sql;
        }

        public String findAll(Map param){
            String sql= new SQL() {{
                SELECT("m.master_bill_id, m.master_bill_type_id, m.pull_dispose_amount, m.pull_dispose_comment, m.create_time,m.create_user_id, t.native_bill_id, t.pull_number, t.pull_actual_weight, t.pull_chargeable_weight, t.pull_volume, t.pull_amount, t.pull_comment, t.pull_dispose_reason_ids, t.pull_dispose_id, t.pull_dispose_airport_id, t.pull_dispose_airlines_id, t.pull_dispose_flight_date, t.pull_dispose_flight_no, t.pull_dispose_flight_name ");
                FROM("b_master_bill_pull_detail t left join b_master_bill_pull m on t.master_bill_pull_id = m.id ");
                WHERE("1 = 1");
            }}.toString();

            // 主单号
            String keyMasterBillCode = DataTypeUtil.toStringObject(param.get("keyMasterBillCode"));

            // 主单类型
            Long keyMasterBillTypeId = DataTypeUtil.toLongObject(param.get("keyMasterBillTypeId"));

            // 日期
            String keyPullStartDate = DataTypeUtil.toStringObject(param.get("keyPullStartDate"));
            String keyPullEndDate = DataTypeUtil.toStringObject(param.get("keyPullEndDate"));

            // 分单号
            String keyNativeBillCode = DataTypeUtil.toStringObject(param.get("keyNativeBillCode"));

            // 获取操作员与操作部门id
            String czyId = DataTypeUtil.toStringObject(param.get("czyId"));
            String departmentId = DataTypeUtil.toStringObject(param.get("departmentId"));

            if(keyMasterBillCode != null){
                sql += " and m.master_bill_id = " + DataTypeUtil.quoteStr(keyMasterBillCode);
            }

            if(keyMasterBillTypeId != null){
                sql += " and m.master_bill_type_id = " + keyMasterBillTypeId;
            }

            if(keyPullStartDate!=null && !"".equals(keyPullStartDate)){
                if(keyPullEndDate!=null && !"".equals(keyPullEndDate)){
                    int d=keyPullStartDate.compareTo(keyPullEndDate);
                    if(d>0){
                        String tempDate = keyPullStartDate;
                        keyPullStartDate = keyPullEndDate;
                        keyPullEndDate = tempDate;
                    }
                    sql += " and (date_format(m.create_time,'%Y-%m-%d') between "+ DataTypeUtil.quoteStr(keyPullStartDate) +" and " + DataTypeUtil.quoteStr(keyPullEndDate) + ")";
                }
                else{
                    sql += " and date_format(m.create_time,'%Y-%m-%d') >= "+ DataTypeUtil.quoteStr(keyPullStartDate);
                }
            }
            else{
                if(keyPullEndDate!=null && !"".equals(keyPullEndDate)){
                    sql += " and date_format(m.create_time,'%Y-%m-%d') <= "+ DataTypeUtil.quoteStr(keyPullEndDate);
                }
            }

            if(keyNativeBillCode != null){
                sql += " and t.native_bill_id = " + DataTypeUtil.quoteStr(keyNativeBillCode);
            }
            return sql;
        }


        /**
         * 350,0,分单配载状态,分单配载状态,XT_DIC_BILL_LOAD_STATUS
         * 351,350,未配载,未配载,""
         * 352,350,部分配载,部分配载,""
         * 353,350,全部配载,全部配载,""
         *
         * 410,0,作废状态,作废状态,XT_DIC_CANCEL_STATUS
         * 411,410,未作废,未作废,
         * 412,410,已作废,已作废,
         *
         * @param param
         * @return
         */
        public String getReloadList(Map param){
            String sql= new SQL() {{
                SELECT("t.* ");
                FROM("b_master_bill_pull_detail t left join b_native_bill s on t.native_bill_id = s.id");
                WHERE("1=1 ");
            }}.toString();

            if(param!=null){
                /**
                 * key
                 * transportSpeciesId
                 * destCityId
                 * airportId
                 * airlinesId
                 * flightDate
                 * flightNo
                 *
                 * existBillIds
                 * 70,0,运输种类,运输种类,XT_DIC_TRANSPORT_SPECIES
                 * 71,70,空运,空运,""
                 * 72,70,汽运,汽运,""
                 */
                String key = DataTypeUtil.toStringObject(param.get("key"));
                Long transportSpeciesId = DataTypeUtil.toLongObject(param.get("transportSpeciesId"));
                String destCityId = DataTypeUtil.toStringObject(param.get("destCityId"));
                String airportId = DataTypeUtil.toStringObject(param.get("airportId"));
                String airlinesId = DataTypeUtil.toStringObject(param.get("airlinesId"));
                String flightDate = DataTypeUtil.toStringObject(param.get("flightDate"));
                String flightNo = DataTypeUtil.toStringObject(param.get("flightNo"));
                String flightName = DataTypeUtil.toStringObject(param.get("flightName"));
                String existBillIds = DataTypeUtil.toStringObject(param.get("existBillIds"));
                Long pullDisposeId = DataTypeUtil.toLongObject(param.get("pullDisposeId")); // 拉货处置方式


                /**
                 * and t.native_bill_id like '%07549%'
                 *   and t.pull_dispose_id = 232
                 *
                 *   and t.pull_dispose_airport_id = '320100'
                 *   and t.pull_dispose_airlines_id = '10016'
                 *   and t.pull_dispose_flight_no = '455087271064781452'
                 *
                 *   and t.pull_dispose_flight_name = 'AQ1033'
                 *   and date_format(t.pull_dispose_flight_date,'%Y-%m-%d') = '2021-05-10'
                 *   and s.dest_city_id = '220100'
                 *   and (s.bill_load_status_id is null or s.bill_load_status_id != 353) and (s.cancel_status_id is null or s.cancel_status_id != 412)
                 */

                if(key!=null){
                    sql += " and t.native_bill_id like " + DataTypeUtil.quoteAllLikeStr(key);
                }

                if(pullDisposeId!=null){
                    sql += " and t.pull_dispose_id = " + pullDisposeId;
                }

                if(transportSpeciesId.longValue() == SysConstant.XT_DIC_TRANSPORT_SPECIES.AIR.getId()){

                    if(airportId!=null){
                        sql += " and t.pull_dispose_airport_id = " + DataTypeUtil.quoteStr(airportId);
                    }

                    if(airlinesId!=null){
                        sql += " and t.pull_dispose_airlines_id = " + DataTypeUtil.quoteStr(airlinesId);
                    }

                    if(flightNo!=null){
                        sql += " and t.pull_dispose_flight_no = " + DataTypeUtil.quoteStr(flightNo);
                    }
                }

                if(flightName!=null){
                    sql += " and t.pull_dispose_flight_name = " + DataTypeUtil.quoteStr(flightName);
                }

                if(flightDate!=null){
                    sql += " and date_format(t.pull_dispose_flight_date,'%Y-%m-%d') = " + DataTypeUtil.quoteStr(flightDate);
                }

                if(destCityId!=null){
                    sql += " and s.dest_city_id = " + DataTypeUtil.quoteStr(destCityId);
                }

                String ids="";
                if(existBillIds!=null){
                    String[] existBillIdArray = existBillIds.split(",");
                    if(existBillIdArray.length>0){
                        for(String billIdValue : existBillIdArray){
                            ids += DataTypeUtil.quoteStr(billIdValue) + ",";
                        }
                    }
                }
                if(ids.length()>0){
                    ids = ids.substring(0,ids.length()-1);
                    sql += " and s.id not in (" + ids + ")";
                }

                sql += " and (s.bill_load_status_id is null or s.bill_load_status_id != 353)";
                sql += " and (s.cancel_status_id is null or s.cancel_status_id != 412)";
            }

            return sql;
        }
    }

    @Results(id = "bMasterBillPullDetailMapperId",value = {
            @Result(column = "id", property = "id"),
            @Result(column = "master_bill_pull_id", property = "masterBillPullId"),
            @Result(column = "native_bill_id", property = "nativeBillId"),
            @Result(column = "pull_number", property = "pullNumber"),
            @Result(column = "pull_actual_weight", property = "pullActualWeight"),
            @Result(column = "pull_chargeable_weight", property = "pullChargeableWeight"),
            @Result(column = "pull_volume", property = "pullVolume"),
            @Result(column = "pull_amount", property = "pullAmount"),
            @Result(column = "pull_comment", property = "pullComment"),

            @Result(column = "pull_dispose_id", property = "pullDisposeId"),
            @Result(column = "pull_dispose_airport_id", property = "pullDisposeAirportId"),
            @Result(column = "pull_dispose_airlines_id", property = "pullDisposeAirlinesId"),
            @Result(column = "pull_dispose_flight_no", property = "pullDisposeFlightNo"),
            @Result(column = "pull_dispose_flight_name", property = "pullDisposeFlightName"),
            @Result(column = "pull_dispose_flight_date", property = "pullDisposeFlightDate"),
            @Result(column = "pull_dispose_reason_ids", property = "pullDisposeReasonIds")

    })
    @SelectProvider(type = BMasterBillPullDetailMapperProvider.class, method = "queryPullDetailList")
    List<BMasterBillPullDetailPojo> queryPullDetailList(Map param);

    @ResultMap("bMasterBillPullDetailMapperId")
    @SelectProvider(type = BMasterBillPullDetailMapperProvider.class, method = "getReloadList")
    List<BMasterBillPullDetailPojo> getReloadList(Map param);


    @ResultType(Map.class)
    @SelectProvider(type = BMasterBillPullDetailMapperProvider.class, method = "findAll")
    List<Map<String, Object>> findAll(Map param);


    @Insert(
            "<script>" +
                    "insert into b_master_bill_pull_detail(id, master_bill_pull_id, native_bill_id, pull_number, pull_actual_weight, pull_chargeable_weight, pull_volume, pull_amount, pull_comment, pull_dispose_id, pull_dispose_airport_id, pull_dispose_airlines_id, pull_dispose_flight_no, pull_dispose_flight_name, pull_dispose_flight_date, pull_dispose_reason_ids) values " +
                    "<foreach collection='bMasterBillPullDetailPojoList' item='pojo' separator=','>" +
                    "(#{pojo.id},#{pojo.masterBillPullId},#{pojo.nativeBillId},#{pojo.pullNumber},#{pojo.pullActualWeight},#{pojo.pullChargeableWeight},#{pojo.pullVolume},#{pojo.pullAmount},#{pojo.pullComment}, #{pojo.pullDisposeId}, #{pojo.pullDisposeAirportId}, #{pojo.pullDisposeAirlinesId}, #{pojo.pullDisposeFlightNo}, #{pojo.pullDisposeFlightName}, #{pojo.pullDisposeFlightDate}, #{pojo.pullDisposeReasonIds})" +
                    "</foreach>" +
                    "</script>"
    )
    @Options(keyProperty = "id")
    int batchInsert(@Param("bMasterBillPullDetailPojoList")  List<BMasterBillPullDetailPojo> bMasterBillPullDetailPojoList);

    @InsertProvider(type = BMasterBillPullDetailMapperProvider.class, method = "insert")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(BMasterBillPullDetailPojo bMasterBillPullDetailPojo);

    @UpdateProvider(type = BMasterBillPullDetailMapperProvider.class, method = "update")
    int update(BMasterBillPullDetailPojo bMasterBillPullDetailPojo);

    @DeleteProvider(type = BMasterBillPullDetailMapperProvider.class, method = "delete")
    int delete(String id);

    @DeleteProvider(type = BMasterBillPullDetailMapperProvider.class, method = "deleteByMasterBillId")
    int deleteByMasterBillId(String masterBillId);
}
